﻿Imports System.Data.SqlClient

Public Class frmOrdenDiesel
    Dim Bitacora As New clsActividad
    Dim Usuario As New clsUsuarioPrincipal

    Private Autobuses As New DataTable("NoEconomico")
    Private Operadores As New DataTable("Nombre")
    Private AutocompletadoAutobuses As New AutoCompleteStringCollection
    Private AutocompletadoOperadores As New AutoCompleteStringCollection

    Dim IdRutaGlobal As Integer

    Private Sub frmOrdenDiesel_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Autobuses.PrimaryKey = Nothing
        Operadores.PrimaryKey = Nothing
        

        Autobuses.Clear()
        Autobuses.Columns.Clear()
        Operadores.Clear()
        Operadores.Columns.Clear()
        
    End Sub

    Private Sub frmOrdenDiesel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip2.Cursor = Cursors.Hand
        ToolStrip2.RenderMode = System.Windows.Forms.ToolStripRenderMode.System
        datAplicacion.Value = Now()

        Bitacora.RegistraActividad("Ingresó al ordenes de combustible")
        Usuario.Ubicacion("Ordenes de combustible")

        txtPorcentaje.Text = 0
        CargaRutas()

        CargaAutobuses()
        Dim claves(0) As DataColumn
        claves(0) = Autobuses.Columns("NoEconomico")
        Autobuses.PrimaryKey = claves

        For Each Autobus In Autobuses.Rows
            AutocompletadoAutobuses.Add(Autobus("NoEconomico"))
        Next

        CargaOperadores()
        Dim claves2(0) As DataColumn
        claves2(0) = Operadores.Columns("Nombre")
        Operadores.PrimaryKey = claves2

        For Each Operador In Operadores.Rows
            AutocompletadoOperadores.Add(Operador("Nombre"))
        Next

        frmMain.statusActividad.Text = ": Órden de Diesel"
    End Sub
    Sub CargaRutas()
        lstRutas.Items.Clear()

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        strSql = "SELECT Nombre FROM Rutas ORDER BY NOMBRE"
        Application.DoEvents()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        cmdBuscar.CommandText = strSql

        Dim rdBuscar As SqlDataReader
        cnconn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        lstRutas.Items.Add("Seleccione una ruta...")

        Do While rdBuscar.Read()
            lstRutas.Items.Add(rdBuscar("Nombre"))
        Loop

        lstRutas.Text = "Seleccione una ruta..."
    End Sub
    Sub CargaAutobuses()
        Autobuses.Columns.Add("NoEconomico")

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        strSql = "SELECT NOECONOMICO FROM AUTOBUSES WHERE ESTATUS=1 GROUP BY NOECONOMICO"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Do While rdBuscar.Read()
            Autobuses.Rows.Add(rdBuscar("NOECONOMICO").ToString.Trim)
        Loop
    End Sub
    Sub CargaOperadores()
        Operadores.Columns.Add("Nombre")

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        strSql = "SELECT NOMBRE FROM OPERADORESGLOBAL WHERE ESTADOEMPLEADO<>'B' GROUP BY NOMBRE"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Do While rdBuscar.Read()
            Operadores.Rows.Add(rdBuscar("NOMBRE").ToString.Trim)
        Loop
    End Sub
    Private Sub ControlEdicion(ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs) Handles dgOrden.EditingControlShowing
        If dgOrden.CurrentCell.ColumnIndex = 2 Then
            DirectCast(e.Control, TextBox).AutoCompleteMode = AutoCompleteMode.SuggestAppend
            DirectCast(e.Control, TextBox).AutoCompleteSource = AutoCompleteSource.CustomSource
            DirectCast(e.Control, TextBox).AutoCompleteCustomSource = AutocompletadoAutobuses
        ElseIf dgOrden.CurrentCell.ColumnIndex = 4 Then
            DirectCast(e.Control, TextBox).AutoCompleteMode = AutoCompleteMode.SuggestAppend
            DirectCast(e.Control, TextBox).AutoCompleteSource = AutoCompleteSource.CustomSource
            DirectCast(e.Control, TextBox).AutoCompleteCustomSource = AutocompletadoOperadores
        Else
            DirectCast(e.Control, TextBox).AutoCompleteMode = Nothing
            'DirectCast(e.Control, TextBox).AutoCompleteSource = Nothing
            'DirectCast(e.Control, TextBox).AutoCompleteCustomSource = Nothing
        End If
    End Sub
    Sub CargaOrdenes()
        dgOrden.Rows.Clear()

        Dim IdRuta As Integer = BuscarIntDatoCitra("SELECT IdRuta FROM Rutas WHERE Nombre='" & lstRutas.Text & "'")
        IdRutaGlobal = IdRuta


        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        strSql = "SELECT IdAutobus,NoEconomico FROM AUTOBUSES WHERE IdRuta=" & IdRuta & " AND ESTATUS=1 ORDER BY NoEconomico"

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim Operador As String = ""
        Dim IdOperador As Integer = 0
        Dim IdOrden As Integer = 0
        Dim IdAutobus As Integer = 0
        Dim Status As String = ""
        Dim Observaciones As String = ""
        Dim Litros As Double = 0
        Dim LongStatus As String = ""

        Dim NuevaFecha1 As String = datAplicacion.Value.ToString("yyyy-dd-MM")
        Dim NuevaFecha2 As String = datAplicacion.Value.AddDays(1).ToString("yyyy-dd-MM")
        NuevaFecha1 = NuevaFecha1 & " 00:00:00"
        NuevaFecha2 = NuevaFecha2 & " 00:00:00"

        Do While rdBuscar.Read()
            IdAutobus = rdBuscar("IdAutobus")
             IdOrden = BuscarIntDatoCitra("SELECT IdOrden FROM OrdenDiesel WHERE IdAutobus=" & IdAutobus & " AND FechaOrden>='" & NuevaFecha1 & "' AND FechaOrden<='" & NuevaFecha2 & "'")
            If IdOrden > 0 Then

                IdOperador = BuscarIntDatoCitra("SELECT IdOperador FROM OrdenDiesel WHERE IdOrden=" & IdOrden)
                Operador = BuscarStrDatoCitra("SELECT Nombre FROM OperadoresGlobal WHERE IdOperador=" & IdOperador)
                Status = BuscarStrDatoCitra("SELECT Status FROM OrdenDiesel WHERE IdOrden=" & IdOrden)
                Observaciones = BuscarStrDatoCitra("SELECT Observaciones FROM OrdenDiesel WHERE IdOrden=" & IdOrden)
                Litros = BuscarDblDatoCitra("SELECT LstOrden FROM OrdenDiesel WHERE IdOrden=" & IdOrden)
            Else
                Operador = BuscarStrDatoCitra("SELECT TOP 1 NOMBREOPERADOR FROM LIQUIDACION WHERE IDAUTOBUS=" & IdAutobus & " ORDER BY IDLIQUIDACION DESC")
                IdOperador = BuscarIntDatoCitra("SELECT IdOperador FROM OperadoresGlobal WHERE Nombre='" & Operador & "'")

                Status = ""
                Observaciones = ""
                Litros = 0
            End If
            Select Case Status
                Case "P"
                    LongStatus = "Pendiente"
                Case "S"
                    LongStatus = "Surtida"
                Case "C"
                    LongStatus = "Cancelada"
                Case "R"
                    LongStatus = "Rechazada"
                Case Else
                    LongStatus = "Sin órden en el día"
            End Select

            If Litros = 500 Then
                dgOrden.Rows.Add(IdOrden, IdAutobus, rdBuscar("NoEconomico"), IdOperador, Operador, 0, True, LongStatus, Observaciones)
            Else
                dgOrden.Rows.Add(IdOrden, IdAutobus, rdBuscar("NoEconomico"), IdOperador, Operador, Litros, False, LongStatus, Observaciones)
            End If

        Loop
    End Sub

    Private Sub lstRutas_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstRutas.SelectedIndexChanged
        If lstRutas.Text <> "Seleccione una ruta..." Then
            CargaOrdenes()
        Else
            dgOrden.Rows.Clear()
        End If
    End Sub

    Private Sub datAplicacion_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles datAplicacion.ValueChanged
        If lstRutas.Text <> "Seleccione una ruta..." Then
            CargaOrdenes()
        Else
            dgOrden.Rows.Clear()
        End If
    End Sub

    Private Sub ToolStripButton3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton3.Click
        Close()

    End Sub
    Private Sub FinDeEdicion(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles dgOrden.CellEndEdit
        'Añadir aqui las validaciones necesarias
        Try
            If dgOrden.CurrentCell.ColumnIndex = 2 Then
                Dim IdOrden As Integer = dgOrden.CurrentRow.Cells(0).Value
                If IdOrden > 0 Then
                    dgOrden.CurrentRow.Cells(2).Value = BuscarStrDatoCitra("SELECT NoEconomico FROM AUTOBUSES A INNER JOIN ORDENDIESEL O ON A.IDAUTOBUS=O.IDAUTOBUS WHERE O.IDORDEN=" & IdOrden)
                    MsgBox("No se puede cambiar el autobus en una orden creada", MsgBoxStyle.Exclamation, "Aviso")
                    Exit Sub
                Else
                    dgOrden.CurrentRow.Cells(1).Value = BuscarIntDatoCitra("SELECT IdAutobus FROM Autobuses WHERE NoEconomico='" & dgOrden.CurrentRow.Cells(2).Value & "' AND ESTATUS=1")

                    If dgOrden.CurrentRow.Cells(1).Value = 0 Then
                        dgOrden.CurrentRow.Cells(2).Value = ""
                        MsgBox("El autobús indicado no existe o esta inactivo", MsgBoxStyle.Exclamation, "Aviso")
                        Exit Sub
                    Else
                        Call SetSqlCitra("UPDATE Autobuses SET IdRuta=" & IdRutaGlobal & " WHERE NoEconomico='" & dgOrden.CurrentRow.Cells(2).Value & "'")
                    End If
                End If
            ElseIf dgOrden.CurrentCell.ColumnIndex = 4 Then
                Dim IdOrden As Integer = dgOrden.CurrentRow.Cells(0).Value
                If IdOrden > 0 Then
                    'ACTUALIZA LA ORDEN PERO VERIFICA EL ESTATUS PRIMERO
                    dgOrden.CurrentRow.Cells(3).Value = BuscarIntDatoCitra("SELECT IdOperador FROM OperadoresGlobal WHERE Nombre='" & dgOrden.CurrentRow.Cells(4).Value & "'")
                    If dgOrden.CurrentRow.Cells(3).Value = 0 Then
                        dgOrden.CurrentRow.Cells(4).Value = ""
                        MsgBox("El operador indicado no existe o se encuentra inactivo", MsgBoxStyle.Exclamation, "Aviso")
                        Exit Sub
                    End If

                    Dim Estatus As String = Mid(dgOrden.CurrentRow.Cells(7).Value, 1)
                    If Estatus = "P" Or Estatus = "R" Then
                        Call SetSqlCitra("UPDATE ORDENDIESEL SET IdOperador=" & dgOrden.CurrentRow.Cells(3).Value & " WHERE IdOrden=" & IdOrden)
                    Else
                        MsgBox("No se puede modificar una órden surtida o cancelada", MsgBoxStyle.Exclamation, "Aviso")
                    End If
                Else
                    dgOrden.CurrentRow.Cells(3).Value = BuscarIntDatoCitra("SELECT IdOperador FROM OperadoresGlobal WHERE Nombre='" & dgOrden.CurrentRow.Cells(4).Value & "'")
                    If dgOrden.CurrentRow.Cells(3).Value = 0 Then
                        dgOrden.CurrentRow.Cells(4).Value = ""
                        MsgBox("El operador indicado no existe o se encuentra inactivo", MsgBoxStyle.Exclamation, "Aviso")
                        Exit Sub
                    End If
                End If
            ElseIf dgOrden.CurrentCell.ColumnIndex = 5 Then
                Dim IdOrden As Integer = dgOrden.CurrentRow.Cells(0).Value
                If IdOrden > 0 Then
                    'ACTUALIZA LA ORDEN PERO VERIFICA EL ESTATUS PRIMERO
                    If dgOrden.CurrentRow.Cells(5).Value < 0 Then
                        dgOrden.CurrentRow.Cells(5).Value = 0
                        MsgBox("Los litros no pueden ser un valor negativo", MsgBoxStyle.Exclamation, "Aviso")
                        Exit Sub
                    End If
                    If dgOrden.CurrentRow.Cells(5).Value > 350 Then
                        dgOrden.CurrentRow.Cells(5).Value = 0
                        MsgBox("Los litros indicados no son válidos", MsgBoxStyle.Exclamation, "Aviso")
                        Exit Sub
                    End If

                    Dim Estatus As String = Mid(dgOrden.CurrentRow.Cells(7).Value, 1)
                    If Estatus = "P" Or Estatus = "R" Then
                        If dgOrden.CurrentRow.Cells(5).Value = 0 Then
                            Call SetSqlCitra("DELETE FROM ORDENDIESEL WHERE IDORDEN=" & IdOrden)
                        Else
                            Call SetSqlCitra("UPDATE ORDENDIESEL SET LstOrden=" & dgOrden.CurrentRow.Cells(5).Value & " WHERE IdOrden=" & IdOrden)
                        End If

                    Else
                        MsgBox("No se puede modificar una órden surtida o cancelada", MsgBoxStyle.Exclamation, "Aviso")
                    End If

                Else
                    If dgOrden.CurrentRow.Cells(5).Value < 0 Then
                        dgOrden.CurrentRow.Cells(5).Value = 0
                        MsgBox("Los litros no pueden ser un valor negativo", MsgBoxStyle.Exclamation, "Aviso")
                        Exit Sub
                    End If
                    If dgOrden.CurrentRow.Cells(5).Value > 350 Then
                        dgOrden.CurrentRow.Cells(5).Value = 0
                        MsgBox("Los litros indicados no son válidos", MsgBoxStyle.Exclamation, "Aviso")
                        Exit Sub
                    End If
                    'INSERTA LA ORDEN VERIFICANDO CAMPOS COMPLETOS
                    If dgOrden.CurrentRow.Cells(1).Value > 0 And dgOrden.CurrentRow.Cells(3).Value > 0 Then
                        Call SetSqlCitra("UPDATE ORDENDIESEL SET STATUS='C',OBSERVACIONES='Nueva orden el día " & datAplicacion.Text & "' WHERE IDAUTOBUS=" & dgOrden.CurrentRow.Cells(1).Value & " AND (STATUS='P' OR STATUS='R')")
                        Dim Orden As New clsOrdenDiesel
                        Orden.IdAutobus = dgOrden.CurrentRow.Cells(1).Value
                        Orden.IdRuta = IdRutaGlobal
                        Orden.IdOperador = dgOrden.CurrentRow.Cells(3).Value
                        Orden.LtsOrden = dgOrden.CurrentRow.Cells(5).Value
                        Orden.LtsDesp = 0
                        Orden.Observaciones = ""
                        Orden.FechaOrden = datAplicacion.Value
                        Orden.FechaSurtido = Now()
                        Orden.Usuario = UsuarioActivo
                        Orden.Status = "P"
                        Orden.Insertar()
                        dgOrden.CurrentRow.Cells(0).Value = Orden.IdOrden
                        dgOrden.CurrentRow.Cells(7).Value = "Pendiente"
                        dgOrden.CurrentRow.Cells(8).Value = ""
                    End If
                End If
            ElseIf dgOrden.CurrentCell.ColumnIndex = 6 Then
                Dim IdOrden As Integer = dgOrden.CurrentRow.Cells(0).Value
                If IdOrden > 0 Then
                    'ACTUALIZA LA ORDEN PERO VERIFICA EL ESTATUS PRIMERO
                    'SI ESTA DESMARCADA BORRA LA ORDEN
                    If dgOrden.CurrentRow.Cells(6).EditedFormattedValue = True Then
                        Dim Estatus As String = Mid(dgOrden.CurrentRow.Cells(7).Value, 1)
                        If Estatus = "P" Or Estatus = "R" Then
                            Call SetSqlCitra("UPDATE ORDENDIESEL SET LstOrden=500 WHERE IdOrden=" & IdOrden)
                        Else
                            MsgBox("No se puede modificar una órden surtida o cancelada", MsgBoxStyle.Exclamation, "Aviso")
                        End If
                    Else
                        Dim Estatus As String = Mid(dgOrden.CurrentRow.Cells(7).Value, 1)
                        If Estatus = "P" Or Estatus = "R" Then
                            Call SetSqlCitra("DELETE FROM ORDENDIESEL WHERE IDORDEN=" & IdOrden)
                            dgOrden.CurrentRow.Cells(0).Value = 0
                            dgOrden.CurrentRow.Cells(5).Value = 0
                            dgOrden.CurrentRow.Cells(7).Value = "Sin órden en el día"
                        Else
                            MsgBox("No se puede modificar una órden surtida o cancelada", MsgBoxStyle.Exclamation, "Aviso")
                        End If
                        
                    End If
                   
                Else
                    'INSERTA LA ORDEN VERIFICANDO CAMPOS COMPLETOS
                    If dgOrden.CurrentRow.Cells(6).EditedFormattedValue = True Then
                        If dgOrden.CurrentRow.Cells(1).Value > 0 And dgOrden.CurrentRow.Cells(3).Value > 0 Then
                            Call SetSqlCitra("UPDATE ORDENDIESEL SET STATUS='C',OBSERVACIONES='Nueva orden el día " & datAplicacion.Text & "' WHERE IDAUTOBUS=" & dgOrden.CurrentRow.Cells(1).Value & " AND (STATUS='P' OR STATUS='R')")
                            Dim Orden As New clsOrdenDiesel
                            Orden.IdAutobus = dgOrden.CurrentRow.Cells(1).Value
                            Orden.IdRuta = IdRutaGlobal
                            Orden.IdOperador = dgOrden.CurrentRow.Cells(3).Value
                            Orden.LtsOrden = 500
                            Orden.LtsDesp = 0
                            Orden.Observaciones = ""
                            Orden.FechaOrden = datAplicacion.Value
                            Orden.FechaSurtido = Now()
                            Orden.Usuario = UsuarioActivo
                            Orden.Status = "P"
                            Orden.Insertar()
                            dgOrden.CurrentRow.Cells(0).Value = Orden.IdOrden
                            dgOrden.CurrentRow.Cells(7).Value = "Pendiente"
                            dgOrden.CurrentRow.Cells(8).Value = ""
                        End If
                    End If
                End If

            End If


        Catch ex As Exception
            '.... Accion en caso de excepcion
        End Try
    End Sub

    Private Sub dgOrden_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgOrden.CellContentClick

    End Sub
End Class